package com.yonyou.poi;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Date;


/**
 * @Author wwj
 * @Date 2021/12/15
 * @Description 单元格属性有多种，怎样可以读出不同类型
 */
public class CellTypeTest {

    // 创建一个多数据类型的 03 表
    @Test
    public void create03ExcelTest() throws Exception{

        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        // 第一行给类型
        Row row1 = sheet.createRow(0);

        Cell intCell = row1.createCell(0);
        intCell.setCellValue("整型数字");

        Cell doubleCell = row1.createCell(1);
        doubleCell.setCellValue("浮点型数字");

        Cell stringCell = row1.createCell(2);
        stringCell.setCellValue("字符串");

        Cell booleanCell = row1.createCell(3);
        booleanCell.setCellValue("布尔值");

        Cell dateCell = row1.createCell(4);
        dateCell.setCellValue("日期");

        Cell formulaCell = row1.createCell(5);
        formulaCell.setCellValue("公式");

        // 第二行赋值
        Row row2 = sheet.createRow(1);

        Cell intValue = row2.createCell(0);
        intValue.setCellValue(10);

        Cell doubleValue = row2.createCell(1);
        doubleValue.setCellValue(12.34);

        Cell stringValue = row2.createCell(2);
        stringValue.setCellValue("字符串");

        Cell booleanValue = row2.createCell(3);
        booleanValue.setCellValue(true);

        Cell dateValue = row2.createCell(4);
        dateValue.setCellValue(String.valueOf(new Date()));

        Cell formulaValue = row2.createCell(5);
        formulaValue.setCellFormula("SUM(A2:B2)");

        // 文件输出流
        FileOutputStream fileOutputStream = new FileOutputStream("D:/CMCC/Study/03type.xls");
        workbook.write(fileOutputStream);
        System.out.println("单元格属性表创建成功！");
        fileOutputStream.close();
    }


    // 读取一个多数据类型的表
    @Test
    public void typeReadTest() throws Exception{

        // 从输入流读取文件
        FileInputStream fileInputStream = new FileInputStream("D:/CMCC/Study/03type.xls");
        // 创建工作簿
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        // 依次得到表、行、单元格
        int SheetCount = workbook.getNumberOfSheets();
        for (int sheetNum = 0; sheetNum < SheetCount; sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);

            if (sheet != null) {
                int rowCount = sheet.getPhysicalNumberOfRows();
                for (int rowNum = 0; rowNum < rowCount; rowNum++) {
                    Row row = sheet.getRow(rowNum);

                    if (row != null) {
                        int cellCount = row.getPhysicalNumberOfCells();
                        for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                            Cell cell = row.getCell(cellNum);

                            // 匹配数据类型
                            if (cell != null) {
                                CellType cellType = cell.getCellType();
                                String cellValue = "";

                                switch (cellType) {
                                    case _NONE:
                                        break;
                                    case NUMERIC:
                                        System.out.print("【数字】");
                                        // 如果是日期格式
                                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                            System.out.print("【日期】");
                                            Date dateCellValue = cell.getDateCellValue();
                                            /*cellValue = new Date(dateCellValue).toString("yyyy-MM-dd");*/
                                        }
                                        double doubleCellValue = cell.getNumericCellValue();
                                        cellValue = String.valueOf(doubleCellValue);
                                        break;
                                    case STRING:
                                        System.out.print("【字符串】");
                                        cellValue = cell.getStringCellValue();
                                        break;
                                    case BOOLEAN:
                                        System.out.print("【布尔值】");
                                        boolean booleanCellValue = cell.getBooleanCellValue();
                                        cellValue = String.valueOf(booleanCellValue);
                                        break;
                                    case BLANK:
                                        System.out.print("【空白】");
                                        break;
                                    case FORMULA:
                                        String cellFormula = cell.getCellFormula();
                                        System.out.print("【公式】");

                                        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
                                        CellValue evaluate = formulaEvaluator.evaluate(cell);
                                        cellValue = evaluate.formatAsString();
                                        break;
                                }
                                System.out.println(cellValue);
                            }
                        }
                    }
                }
            }
        }

        fileInputStream.close();
    }
}
